Telegram Group & Telegram Channel
🖥 Задача: Анализ пользовательского поведения с аномалиями в SQL

## Условие задачи:

Дана таблица user_events со следующей структурой:


CREATE TABLE user_events (
user_id INT,
event_time TIMESTAMP,
event_type VARCHAR(50),
platform VARCHAR(50)
);


🎯 Каждая строка описывает событие пользователя:
- user_id — идентификатор пользователя,
- event_time — время события,
- event_type — тип события (`login`, purchase, logout, error и т.д.),
- platform — платформа (`iOS`, Android, `Web`).

Требуется:

1. Найти пользователей, которые:
- Выполнили покупку (`purchase`),
- Но не заходили в систему (`login`) в течение последних 7 дней перед покупкой.

2. Найти пользователей, у которых:
- Более 30% всех событий за последний месяц составляют события типа error.

3. Рассчитать для каждого пользователя:
- Среднее время между входом (`login`) и следующим выходом (`logout`).
- Если logout отсутствует после login — игнорировать такую сессию.

---

## Дополнительные условия:

- Считайте, что данные могут быть объемными: миллионы строк.
- Решение должно быть оптимизировано: избегайте подзапросов в подзапросах без индексов, старайтесь минимизировать количество проходов по данным.
- Можно использовать оконные функции (`WINDOW FUNCTIONS`) и временные таблицы (`CTE`) для упрощения запросов.
- Платформу можно игнорировать в расчетах.

---

## Что оценивается:

- Умение использовать оконные функции и агрегаты.
- Умение правильно интерпретировать условия задачи в SQL-операции.
- Оптимизация запросов под большие объемы данных.
- Чистота, читаемость и структурированность кода SQL-запросов.

---

Примечание:
Эта задача проверяет как технические навыки работы с SQL, так и внимательность к деталям формулировки задачи. Небрежная реализация может дать неверные результаты, особенно на больших данных.

🔥 Подсказки и намёки для решения задачи


## Задание 1: Найти пользователей с покупками без логина за последние 7 дней

**Намёк:**
- Используйте оконную функцию LAG() или MAX() с фильтрацией событий login.
- Для каждой покупки проверяйте, был ли login в пределах 7 дней до события purchase.
- Можно применить LEFT JOIN событий login к событиям purchase.

## Задание 2: Найти пользователей с долей ошибок > 30%

**Намёк:**
- Используйте оконные функции COUNT(*) и SUM(CASE WHEN event_type = 'error' THEN 1 ELSE 0 END).
- Постройте долю ошибок на основе всех событий пользователя за последние 30 дней (`WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'`).

## Задание 3: Рассчитать среднее время между login и следующим logout

**Намёк:**
- Используйте оконную функцию LEAD() для поиска следующего события после login.
- Пара login -> logout должна иметь корректный порядок по времени.
- Отбрасывайте случаи, где следующего logout нет или это событие другого типа.

@sqlhub
Please open Telegram to view this post
VIEW IN TELEGRAM



tg-me.com/sqlhub/1857
Create:
Last Update:

🖥 Задача: Анализ пользовательского поведения с аномалиями в SQL

## Условие задачи:

Дана таблица user_events со следующей структурой:


CREATE TABLE user_events (
user_id INT,
event_time TIMESTAMP,
event_type VARCHAR(50),
platform VARCHAR(50)
);


🎯 Каждая строка описывает событие пользователя:
- user_id — идентификатор пользователя,
- event_time — время события,
- event_type — тип события (`login`, purchase, logout, error и т.д.),
- platform — платформа (`iOS`, Android, `Web`).

Требуется:

1. Найти пользователей, которые:
- Выполнили покупку (`purchase`),
- Но не заходили в систему (`login`) в течение последних 7 дней перед покупкой.

2. Найти пользователей, у которых:
- Более 30% всех событий за последний месяц составляют события типа error.

3. Рассчитать для каждого пользователя:
- Среднее время между входом (`login`) и следующим выходом (`logout`).
- Если logout отсутствует после login — игнорировать такую сессию.

---

## Дополнительные условия:

- Считайте, что данные могут быть объемными: миллионы строк.
- Решение должно быть оптимизировано: избегайте подзапросов в подзапросах без индексов, старайтесь минимизировать количество проходов по данным.
- Можно использовать оконные функции (`WINDOW FUNCTIONS`) и временные таблицы (`CTE`) для упрощения запросов.
- Платформу можно игнорировать в расчетах.

---

## Что оценивается:

- Умение использовать оконные функции и агрегаты.
- Умение правильно интерпретировать условия задачи в SQL-операции.
- Оптимизация запросов под большие объемы данных.
- Чистота, читаемость и структурированность кода SQL-запросов.

---

Примечание:
Эта задача проверяет как технические навыки работы с SQL, так и внимательность к деталям формулировки задачи. Небрежная реализация может дать неверные результаты, особенно на больших данных.

🔥 Подсказки и намёки для решения задачи


## Задание 1: Найти пользователей с покупками без логина за последние 7 дней

**Намёк:**
- Используйте оконную функцию LAG() или MAX() с фильтрацией событий login.
- Для каждой покупки проверяйте, был ли login в пределах 7 дней до события purchase.
- Можно применить LEFT JOIN событий login к событиям purchase.

## Задание 2: Найти пользователей с долей ошибок > 30%

**Намёк:**
- Используйте оконные функции COUNT(*) и SUM(CASE WHEN event_type = 'error' THEN 1 ELSE 0 END).
- Постройте долю ошибок на основе всех событий пользователя за последние 30 дней (`WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'`).

## Задание 3: Рассчитать среднее время между login и следующим logout

**Намёк:**
- Используйте оконную функцию LEAD() для поиска следующего события после login.
- Пара login -> logout должна иметь корректный порядок по времени.
- Отбрасывайте случаи, где следующего logout нет или это событие другого типа.

@sqlhub

BY Data Science. SQL hub


Warning: Undefined variable $i in /var/www/tg-me/post.php on line 283

Share with your friend now:
tg-me.com/sqlhub/1857

View MORE
Open in Telegram


Data Science SQL hub Telegram | DID YOU KNOW?

Date: |

Can I mute a Telegram group?

In recent times, Telegram has gained a lot of popularity because of the controversy over WhatsApp’s new privacy policy. In January 2021, Telegram was the most downloaded app worldwide and crossed 500 million monthly active users. And with so many active users on the app, people might get messages in bulk from a group or a channel that can be a little irritating. So to get rid of the same, you can mute groups, chats, and channels on Telegram just like WhatsApp. You can mute notifications for one hour, eight hours, or two days, or you can disable notifications forever.

What is Secret Chats of Telegram

Secret Chats are one of the service’s additional security features; it allows messages to be sent with client-to-client encryption. This setup means that, unlike regular messages, these secret messages can only be accessed from the device’s that initiated and accepted the chat. Additionally, Telegram notes that secret chats leave no trace on the company’s services and offer a self-destruct timer.

Data Science SQL hub from pl


Telegram Data Science. SQL hub
FROM USA